package com.file.parser;

import com.file.bo.PSBC;
import com.file.bo.PSBCTran;
import com.file.bo.ResponseData;
import com.file.constant.EfpConstants;
import com.file.constant.ErrorCode;
import com.file.util.JsonUtils;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.io.FileUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
import java.util.Objects;

@Slf4j
public class PSBCExcelParser {

	public ResponseData<String> parsePSBCExcelToJson(String filePath) {
		log.info("parsePSBCExcelToJson started, filePath:{}", filePath);
		String json = null;

		try {
			PSBC psbc = parsePSBCExcel(filePath);
			json = JsonUtils.convertObjectToJson(psbc);
		} catch (Exception e) {
			log.error("[OnError]parsePSBCExcelToJson failed, filePath:{}", filePath, e);
			return new ResponseData<String>(null, ErrorCode.FILE_PARSE_EXCEPTION.getCode(),
					ErrorCode.FILE_PARSE_EXCEPTION.getMsg());
		}

		log.info("parsePSBCExcelToJson completed, filePath:{}", filePath);
		return new ResponseData<String>(json, ErrorCode.SUCCESS.getCode(), ErrorCode.SUCCESS.getMsg());
	}

	public PSBC parsePSBCExcel(String filePath) {
		PSBC psbc = null;
		try (FileInputStream fis = new FileInputStream(new File(filePath));
				HSSFWorkbook workbook = new HSSFWorkbook(fis)) {
			HSSFSheet sheet = workbook.getSheetAt(0);
			psbc = new PSBC();
			List<PSBCTran> psbcTrans = new ArrayList<>();
			for (int i = 0; i < sheet.getLastRowNum(); i++) {
				Row row = sheet.getRow(i);
				if (i == 1) {
					psbc.setAccountNumber(row.getCell(0) != null ? row.getCell(0).getStringCellValue() : "");
					psbc.setTransactionType(row.getCell(1) != null ? row.getCell(1).getStringCellValue() : "");
					psbc.setTransferInAccountNumber(row.getCell(2) != null ? row.getCell(2).getStringCellValue() : "");
					psbc.setIncomeExpense(row.getCell(3) != null ? row.getCell(3).getStringCellValue() : "");
					psbc.setStartDate(row.getCell(4) != null ? row.getCell(4).getStringCellValue() : "");
					psbc.setExpirationDate(row.getCell(5) != null ? row.getCell(5).getStringCellValue() : "");
					psbc.setTotalIncome(row.getCell(6) != null ? this.getStringCellValue(row.getCell(6)) : "");
					psbc.setTotalExpense(row.getCell(7) != null ? this.getStringCellValue(row.getCell(7)) : "");
				}

				if (i >= 3) {
					PSBCTran psbcTran = new PSBCTran();
					psbcTran.setTransactionDate(row.getCell(0) != null ? row.getCell(0).getStringCellValue() : "");
					psbcTran.setPaymentAccountName(row.getCell(1) != null ? row.getCell(1).getStringCellValue() : "");
					psbcTran.setPayeeAccountNumber(row.getCell(2) != null ? row.getCell(2).getStringCellValue() : "");
					psbcTran.setIncomeExpense(row.getCell(3) != null ? row.getCell(3).getStringCellValue() : "");
					psbcTran.setTransactionAmount(row.getCell(4) != null ? this.getStringCellValue(row.getCell(4)) : "");
					psbcTran.setBalance(row.getCell(5) != null ? this.getStringCellValue(row.getCell(5)) : "");
					psbcTran.setSummary(row.getCell(6) != null ? row.getCell(6).getStringCellValue() : "");
					psbcTran.setComment(row.getCell(7) != null ? row.getCell(7).getStringCellValue() : "");
					psbcTrans.add(psbcTran);
				}
			}
			psbc.setPsbcTrans(psbcTrans);
		} catch (Exception e) {
			log.error("[OnError]parsePSBCExcel failed", e);
			throw new RuntimeException(e);
		}

		return psbc;
	}
	
	private String getStringCellValue(Cell cell) {
        String cellStr;
        if (Objects.equals(cell.getCellType(), CellType.NUMERIC)) {
                cellStr = String.valueOf(cell.getNumericCellValue());
        } else {
                cellStr = cell.getStringCellValue();
        }
        return cellStr;
}

	public static void main(String[] args) throws IOException {
		String pdfFilePath = "E:\\data\\files\\PSBC\\邮储流水.xls";
//		String jsonFilePath = "E:\\data\\files\\PSBC\\邮储流水.json";
		
//		String pdfFilePath = "E:\\data\\files\\PSBC\\383566684931231510_9a30509eeef142d878402bfc05c6a9c1_psbc_jyls-1.xls";
		String jsonFilePath = "E:\\data\\files\\PSBC\\383566684931231510_9a30509eeef142d878402bfc05c6a9c1_psbc_jyls-1.json";
		
		
		File jsonFile = new File(jsonFilePath);

		PSBCExcelParser psbcExcelParser = new PSBCExcelParser();
		ResponseData<String> responseData = psbcExcelParser.parsePSBCExcelToJson(pdfFilePath);
		log.info(responseData.getData());
		FileUtils.write(jsonFile, responseData.getData(), EfpConstants.UTF_8);
		jsonFile.createNewFile();
	}

}
